﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;

namespace Codes {
    public class Navigation {

        public static bool add(string name, string url, string target, string state, string group, string order, string pyname, string parent, string tag) {
            string sql="insert into Navigation([name],[url],[target],[state],[group],[order],[pyname],[parent],[tag],[addtime]) values(@name,@url,@target,@state,@group,@order,@pyname,@parent,@tag,now())";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Manager, sql, new OleDbParameter[]{
                new OleDbParameter("@name", name),
                new OleDbParameter("@url", url),
                new OleDbParameter("@target", target),
                new OleDbParameter("@state", state),
                new OleDbParameter("@group", group),
                new OleDbParameter("@order", order),
                new OleDbParameter("@pyname", pyname),
                new OleDbParameter("@parent", parent),
                new OleDbParameter("@tag", tag)
            })>0;
        }
        public static bool update(string name, string url, string target, string state, string group, string order, string pyname, string parent, string tag, string id) {
            string sql="update [Navigation] set [name]=@name,[url]=@url,[target]=@target,[state]=@state,[group]=@group,[order]=@order,[pyname]=@pyname,[parent]=@parent,[tag]=@tag where [ID]=@ID";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Manager, sql, new OleDbParameter[]{
                new OleDbParameter("@name", name),
                new OleDbParameter("@url", url),
                new OleDbParameter("@target", target),
                new OleDbParameter("@state", state),
                new OleDbParameter("@group", group),
                new OleDbParameter("@order", order),
                new OleDbParameter("@pyname", pyname),
                new OleDbParameter("@parent", parent),
                new OleDbParameter("@tag", tag),
                new OleDbParameter("@ID", id)
            })>0;
        }
        public static bool changeOrder(int Order, int ID) {
            string sql="update Navigation set [order]=@Order where ID=@ID";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Manager, sql, new OleDbParameter[]{
                new OleDbParameter("@order", Order),
                new OleDbParameter("@ID", ID)
            })>0;
        }

        public static bool delete(string id) {
            string sql="delete from Navigation where ID="+id+";";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Manager, sql)>0;
        }
        public static System.Data.DataTable getList(int pageindex, int pagesize) {
            int notTop=((pageindex-1)*pagesize);
            string sql=string.Empty;
            if(notTop==0) {
                sql="select top "+pagesize+" * from Navigation order by [order] asc";
            } else {
                sql ="select top "+pagesize+" * from Navigation where id not in(select top "+((pageindex-1)*pagesize)+" id from Navigation) order by [order] asc";
            }
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Manager, sql).Tables[0];
        }

        public static System.Data.DataTable getList(string state="") {
            string sql="select * from Navigation "+(string.IsNullOrEmpty(state)?"":" where [state]="+state)+" order by [addtime] asc,[order] asc";
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Manager, sql).Tables[0];
        }



        public static System.Data.DataTable getList(string parent, string state="") {
            string sql="select *,(select count(1) from Navigation as b where b.[parent]=Navigation.[ID] ) as scount from Navigation where [parent]="+parent+(string.IsNullOrEmpty(state)?"":" and [state]="+state)+" order by [order] asc";
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Manager, sql).Tables[0];
        }

        public static System.Data.DataTable getGroupList(string groupName, string state="") {
            string sql="select * from Navigation where [group] like '*"+groupName+"*'"+(string.IsNullOrEmpty(state)?"":" and [state]="+state)+" order by [order] asc";
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Manager, sql).Tables[0];
        }

        public static System.Data.DataTable getInfo(string id) {
            string sql="select * from Navigation where [id]="+id;
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Manager, sql).Tables[0];
        }

        public static System.Data.DataRow getInfo(string colName, string id) {
            string sql="select "+colName+" from Navigation where [id]="+id;
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Manager, sql).Tables[0].Rows[0];
        }
    }
}